package com.book.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.book.bean.Book;
import com.book.bean.Messagess;
import com.book.bean.PageVo;
import com.book.bean.Reply;
import com.book.dao.MessageDao;
import com.book.util.DBUtil;

public class MessageDaoImpl implements MessageDao {

	@Override
	public List<Messagess> listAllMsg() throws Exception {
		List<Messagess> list = new ArrayList<Messagess>();
		Connection conn = DBUtil.getConn();
		String sql = "select mid,user,mtime,title,content from messagess where isView=0 and isModify=0";
		PreparedStatement psmt = conn.prepareStatement(sql);
		ResultSet rs = psmt.executeQuery();
		while(rs.next()){
			Messagess msg = new Messagess();
			msg.setMid(rs.getInt(1));
			msg.setUser(rs.getString(2));
			msg.setMtime(rs.getString(3));
			msg.setTitle(rs.getString(4));
			msg.setContent(rs.getString(5));
			list.add(msg);
		}
		return list;
	}

	@Override
	public List<Messagess> listMyMsg(String uname) throws Exception {
		List<Messagess> list = new ArrayList<Messagess>();
		
		Connection conn = DBUtil.getConn();
		String sql = "select mid,user,mtime,title,content from messagess where user=? and isView=0 and isModify=0";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setString(1, uname);;
		ResultSet rs = psmt.executeQuery();
		while(rs.next()){
			Messagess msg = new Messagess();
			msg.setMid(rs.getInt(1));
			msg.setUser(rs.getString(2));
			msg.setMtime(rs.getString(3));
			msg.setTitle(rs.getString(4));
			msg.setContent(rs.getString(5));
			list.add(msg);
		}
		return list;
	}

	@Override
	public boolean addMessage(Messagess message) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql = "insert into messagess(user,mtime,title,content,isView,isModify) values(?,?,?,?,0,0)";
		
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(message.getUser());
		param.add(message.getMtime());
		param.add(message.getTitle());
		param.add(message.getContent());
		
		return DBUtil.add(sql, param) > 0;
	}

	@Override
	public boolean modifyMessage(int mid) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql = "update messagess set isModify=1 where mid=?";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(mid);
		return DBUtil.update(sql, param)>0;
	}

	@Override
	public boolean deleteMessage(int mid) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql = "update messagess set isView=1 where mid=?";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(mid);
		return DBUtil.update(sql, param)>0;
		
	}

	@Override
	public Messagess listMsgById(int mid) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql = "select mid,user,mtime,title,content from messagess where mid=? and isModify=0";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, mid);
		ResultSet rs = psmt.executeQuery();
		while (rs.next()) {
			Messagess msg = new Messagess();
			msg.setMid(rs.getInt(1));
			msg.setUser(rs.getString(2));
			msg.setMtime(rs.getString(3));
			msg.setTitle(rs.getString(4));
			msg.setContent(rs.getString(5));
			return msg;
		}
		rs.close();
		psmt.close();
		return null;
	}

	@Override
	public PageVo<Messagess> listMyMsg(int pageIndex, int pageSize, String uname)
			throws Exception {
		Connection conn = DBUtil.getConn();
		String sql = "select mid,user,mtime,title,content from messagess where user=? and isView=0 and isModify=0 order by mid limit ?,?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setString(1, uname);;
		psmt.setInt(2, (pageIndex-1)*pageSize);
		psmt.setInt(3, pageSize);
		List<Messagess> msglist = new ArrayList<Messagess>();
		ResultSet rs = psmt.executeQuery();
		while(rs.next()){
			Messagess msg = new Messagess();
			msg.setMid(rs.getInt(1));
			msg.setUser(rs.getString(2));
			msg.setMtime(rs.getString(3));
			msg.setTitle(rs.getString(4));
			msg.setContent(rs.getString(5));
			msglist.add(msg);
		}
		PageVo<Messagess> pv = new PageVo<Messagess>();
		pv.setPageIndex(pageIndex);
		pv.setPageSize(pageSize);
		int total=getCount()%pageSize==0?getCount()/pageSize:getCount()/pageSize+1;
		pv.setTotalPage(total);
		pv.setPageList(msglist);

		return pv;
	}

	@Override
	public int getCount() throws Exception {
		Connection conn = DBUtil.getConn();
		//准备sql
		String sql = "select count(*) from messagess where isView=0 and isModify=0";
		//获取操作对象
		PreparedStatement psmt = conn.prepareStatement(sql);
		//结果集
		ResultSet rs = psmt.executeQuery();
		if (rs.next()) {
			return rs.getInt(1);
		}
		return 0;
	}

	@Override
	public PageVo<Messagess> listAllMsg(int pageIndex, int pageSize)
			throws Exception {
		Connection conn = DBUtil.getConn();
		String sql = "select mid,user,mtime,title,content from messagess where isView=0 and isModify=0 order by mid limit ?,?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, (pageIndex-1)*pageSize);
		psmt.setInt(2, pageSize);
		List<Messagess> msglist = new ArrayList<Messagess>();
		ResultSet rs = psmt.executeQuery();
		while(rs.next()){
			Messagess msg = new Messagess();
			msg.setMid(rs.getInt(1));
			msg.setUser(rs.getString(2));
			msg.setMtime(rs.getString(3));
			msg.setTitle(rs.getString(4));
			msg.setContent(rs.getString(5));
			msglist.add(msg);
		}
		PageVo<Messagess> pv = new PageVo<Messagess>();
		pv.setPageIndex(pageIndex);
		pv.setPageSize(pageSize);
		int total=getCount()%pageSize==0?getCount()/pageSize:getCount()/pageSize+1;
		pv.setTotalPage(total);
		pv.setPageList(msglist);

		return pv;
	}

	@Override
	public Messagess getOneMesssage(int mid) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql = "select * from messagess where mid=?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, mid);
		ResultSet rs = psmt.executeQuery();
		while (rs.next()) {
			Messagess mes = new Messagess();
			mes.setMid(rs.getInt(1));
			mes.setUser(rs.getString(2));
			mes.setMtime(rs.getString(3));
			mes.setTitle(rs.getString(4));
			mes.setContent(rs.getString(5));
			return mes;
		}
		rs.close();
		psmt.close();
		return null;
	}

	@Override
	public boolean addReply(Reply reply) throws Exception {
		Connection conn = DBUtil.getConn();
		String sql = "insert into reply(reply_id,reply_content,reply_time,isView) values(?,?,?,0)";
		
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(reply.getReply_id());
		param.add(reply.getReply_content());
		param.add(reply.getReply_time());
		
		String sql1 = "insert into message_reply(mid,reply_id) values(?,?)";
		
		ArrayList<Object> param1 = new ArrayList<Object>();
		param1.add(reply.getMessagess().getMid());
		param1.add(reply.getReply_id());
		
		DBUtil.add(sql1, param1);
		return DBUtil.add(sql, param) > 0;
	}

	@Override
	public List<Reply> listReply(int mid) throws Exception {
		List<Reply> list = new ArrayList<Reply>();
		
		Connection conn = DBUtil.getConn();
		String sql = "select reply_content,reply_time from reply r,message_reply mr where r.reply_id=mr.reply_id and mr.mid=?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setInt(1, mid);;
		ResultSet rs = psmt.executeQuery();
		while(rs.next()){
			Reply reply = new Reply();
			reply.setReply_content(rs.getString(1));
			reply.setReply_time(rs.getString(2));
			list.add(reply);
		}
		return list;
	}

}
